package afcp.alumni.file;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import javax.inject.Inject;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import net.sourceforge.stripes.action.Message;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.stereotype.Component;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;

import afcp.alumni.model.Pb;
import afcp.alumni.model.db.FileColumn;
import afcp.alumni.model.db.Member;
import afcp.alumni.model.db.MemberUtils.ColumnNumToValueConvertor;
import afcp.alumni.qualifiers.ExcelFileUploadServiceQualifier;
import afcp.alumni.service.FileUploadService;
import afcp.alumni.service.UploadService.Uploader;

@Component("ExcelFileUploadService")
@ExcelFileUploadServiceQualifier
public class ExcelFileUploadService implements FileUploadService {
	
	private static final Log logger = LogFactory.getLog(ExcelFileUploadService.class);

	@Inject
	private UploadServiceBlobstore uploadService;
	
	private static final int defaultMinColumns = 5;
	
	private static final int FIELD_MAX_LENGTH = 500;

	/**
	 * cf {@link SimpleExcelMemberFileUploadService} pour l'implémentation.
	 * @throws SAXException 
	 * @throws ParserConfigurationException 
	 * @throws OpenXML4JException 
	 */
	@Override
	public void handle(InputStream is, AlumniFile file, List<Message> failures)
			throws IncorrectHeaderException, IOException, OpenXML4JException,
			ParserConfigurationException, SAXException {
		if (!(file instanceof ExcelFile)) {
			throw new RuntimeException("the file object to upload is not an "
					+ "instance of ExcelFile, but the ExcelFileUploadService"
					+ " was called.");
		}
		
		logger.info("ExcelFileUploadService : read file and add members.");

		this.process(is, (ExcelFile) file, failures);

	}
	


	/**
	 * The type of the data value is indicated by an attribute on the cell. The
	 * value is usually in a "v" element within the cell.
	 */
	enum xssfDataType {
		BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,
	}

	/**
	 * Derived from http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api
	 * <p/>
	 * Also see Standard ECMA-376, 1st edition, part 4, pages 1928ff, at
	 * http://www.ecma-international.org/publications/standards/Ecma-376.htm
	 * <p/>
	 * A web-friendly version is http://openiso.org/Ecma/376/Part4
	 */
	class MyXSSFSheetHandler extends DefaultHandler {

		/**
		 * Table with styles
		 */
		private StylesTable stylesTable;

		/**
		 * Table with unique strings
		 */
		private ReadOnlySharedStringsTable sharedStringsTable;

//		/**
//		 * Destination for data
//		 */
//		private final PrintStream output;

		/**
		 * Number of columns to read starting with leftmost
		 */
		private final int minColumnCount;

		// Set when V start element is seen
		private boolean vIsOpen;

		// Set when cell start element is seen;
		// used when cell close element is seen.
		private xssfDataType nextDataType;

		// Used to format numeric cell values.
		private short formatIndex;
		private String formatString;
		private final DataFormatter formatter;

		private int thisColumn = -1;
		// The last column printed to the output stream
		private int lastColumnNumber = -1;
		private Member member = new Member();
		private boolean isHeaderLine = true;
		private boolean emptyLine = true;
		
//		private List<String> columnNames;
		private ColumnNumToValueConvertor conv;
		
		List<Message> failures;

		// Gathers characters as they are seen.
		private StringBuffer value;
		
		private Uploader uploader = new Uploader();

		/**
		 * Accepts objects needed while parsing.
		 * 
		 * @param styles
		 *            Table of styles
		 * @param strings
		 *            Table of shared strings
		 * @param cols
		 *            Minimum number of columns to show
		 * @param target
		 *            Sink for output
		 */
		public MyXSSFSheetHandler(StylesTable styles,
				ReadOnlySharedStringsTable strings, int cols, AlumniFile file,
				List<Message> failures) {
			this.stylesTable = styles;
			this.sharedStringsTable = strings;
			this.minColumnCount = cols;
			this.value = new StringBuffer();
			this.nextDataType = xssfDataType.NUMBER;
			this.formatter = new DataFormatter();
			this.failures = failures;

			List<FileColumn> columns = file.getHeaders();
			

			conv = new ColumnNumToValueConvertor(columns);
			
//			columnNames = new ArrayList<>(columns.size());
//			for (FileColumn column : columns) {
//				if (column == null) {
//					throw new NullPointerException("A column is null in the "
//							+ "list while parsing the form. One of the "
//							+ "columns is not correctly specified in the "
//							+ "upload form.");
//				}
//				int pos = column.getPosition();
//				while (columnNames.size() < pos) {
//					columnNames.add(null);
//				}
//				// 0-based
//				columnNames.set(pos - 1, column.getId());
//			}
		}

		/*
		 * (non-Javadoc)
		 * 
		 * @see
		 * org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String,
		 * java.lang.String, java.lang.String, org.xml.sax.Attributes)
		 */
		public void startElement(String uri, String localName, String name,
				Attributes attributes) throws SAXException {

			if ("inlineStr".equals(name) || "v".equals(name)) {
				vIsOpen = true;
				// Clear contents cache
				value.setLength(0);
			}
			// c => cell
			else if ("c".equals(name)) {
				// Get the cell reference
				String r = attributes.getValue("r");
				thisColumn = refToColumn(r);
//				thisRow = refToRow(r);

				// Set up defaults.
				this.nextDataType = xssfDataType.NUMBER;
				this.formatIndex = -1;
				this.formatString = null;
				String cellType = attributes.getValue("t");
				String cellStyleStr = attributes.getValue("s");
				if ("b".equals(cellType))
					nextDataType = xssfDataType.BOOL;
				else if ("e".equals(cellType))
					nextDataType = xssfDataType.ERROR;
				else if ("inlineStr".equals(cellType))
					nextDataType = xssfDataType.INLINESTR;
				else if ("s".equals(cellType))
					nextDataType = xssfDataType.SSTINDEX;
				else if ("str".equals(cellType))
					nextDataType = xssfDataType.FORMULA;
				else if (cellStyleStr != null) {
					// It's a number, but almost certainly one
					// with a special style or format
					int styleIndex = Integer.parseInt(cellStyleStr);
					XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
					this.formatIndex = style.getDataFormat();
					this.formatString = style.getDataFormatString();
					if (this.formatString == null)
						this.formatString = BuiltinFormats
								.getBuiltinFormat(this.formatIndex);
				}
			}

		}

		/*
		 * (non-Javadoc)
		 * 
		 * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String,
		 * java.lang.String, java.lang.String)
		 */
		public void endElement(String uri, String localName, String name)
				throws SAXException {

			// v => contents of a cell
			if ("v".equals(name)) {
				
				String cellValue = getCellValue();
				
				if (cellValue == null) {
					return;
				}
				
				if (emptyLine) {
					emptyLine = false;
				}
				

				// Output after we've seen the string contents
				// Emit commas for any fields that were missing on this row
				if (lastColumnNumber == -1) {
					lastColumnNumber = 0;
				}
				
//				System.out.println(cellValue);
				if (isHeaderLine) {
//					for (int i = lastColumnNumber; i < thisColumn - 1; ++i) {
//						headers.add(null);
//					}
//					headers.add(cellValue);
					
					// TODO construire un tableau num de colonne -> id d'en-tête
					// plus direct que l'usage de HashMap pour chaque ligne du
					// fichier.
					// TODO add header checks so that IncorrectHeaderException
					// makes sense
				} else {
					addCellValueToMember(cellValue);
				}

				// Update column
				if (thisColumn > -1)
					lastColumnNumber = thisColumn;

			} else if ("row".equals(name)) {

				// Print out any missing commas if needed
				if (minColumnCount > 0) {
					// Columns are 0 based
					if (lastColumnNumber == -1) {
						lastColumnNumber = 0;
					}
				}

				// We're onto a new row
				lastColumnNumber = -1;
				
				if (isHeaderLine) {
					isHeaderLine = false;
				} else if (!emptyLine) {
					
					checkEmptyFields();
					
//					String memberId = member.generateStringId();
//					if (memberId != null) {

//						Login login = new Login(member);
//						member.setLogin(login);
						
						try {
							uploadService.uploadMember(member, failures, uploader);
						} catch (Exception e) {
							logger.warn("Could not upload the member: " + member, e);
							member.addWarning(Pb.PROBLEM_WITH_DATABASE);
						}
//					}
					
					member = new Member();
					emptyLine = true;
				}
//				System.out.println("######### NEWLINE #########");
			}

		}
		
		private void checkEmptyFields() {
			if (empty(member.getEmail())) {
				member.addError(Pb.NO_EMAIL);
			}
		}
		
		private String getCellValue() {
			String thisStr;

			// Process the value contents as required.
			// Do now, as characters() may be called more than once
			switch (nextDataType) {

			case BOOL:
				char first = value.charAt(0);
				thisStr = first == '0' ? "false" : "true";
				break;

			case ERROR:
				thisStr = "ERROR:" + value.toString();
				break;

			case FORMULA:
				// A formula could result in a string value,
				// so always add double-quote characters.
				thisStr = value.toString();
				break;

			case INLINESTR:
				// TODO: have seen an example of this, so it's untested.
				XSSFRichTextString rtsi = new XSSFRichTextString(
						value.toString());
				thisStr = rtsi.toString();
				break;

			case SSTINDEX:
				String sstIndex = value.toString();
				try {
					int idx = Integer.parseInt(sstIndex);
					XSSFRichTextString rtss = new XSSFRichTextString(
							sharedStringsTable.getEntryAt(idx));
					thisStr = rtss.toString();
				} catch (NumberFormatException ex) {
					logger.info("Failed to parse SST index '" + sstIndex
							+ "': " + ex.toString());
					thisStr = "ERROR:NumberFormatException";
				}
				break;

			case NUMBER:
				String n = value.toString();
				if (this.formatString != null)
					thisStr = formatter.formatRawCellContents(
							Double.parseDouble(n), this.formatIndex,
							this.formatString);
				else
					thisStr = n;
				break;

			default:
				logger.info("(TODO: Unexpected type while parsing Excel file: " + nextDataType + ")");
				thisStr = null;
				break;
			}
			
			if (thisStr.length() > FIELD_MAX_LENGTH) {
				thisStr = thisStr.substring(0, FIELD_MAX_LENGTH);
				member.addWarning(Pb.TOO_LONG_FIELD);
			}
			
			return thisStr.trim();
		}
		
		private void addCellValueToMember(String cellValue) {
//			String header = headers.get(thisColumn);
//			if (header != null) {
				// TODO remplacer ces 2 get() par un seul dans une liste
				// construite en lisant les en-têtes.
//				String headKey = invertedExpectedHeaders.get(header);
			
			
			conv.set(thisColumn, member, cellValue, formatString);
			
			
//				if (thisColumn >= columnNames.size()) {
//					return;
//				}
//			
//				String headKey = columnNames.get(thisColumn);
//				
//				if (headKey != null) {
//
//					switch (headKey) {
//					case MemberUtils.COL_LASTNAME:
//						member.setLastName(cellValue);
//						break;
//					case MemberUtils.COL_FIRSTNAME:
//						member.setFirstName(cellValue);
//						break;
//					case MemberUtils.COL_SEX:
//						MemberUtils.setSex(member, cellValue);
//						break;
//					case MemberUtils.COL_CHINESE_NAME:
//						member.setChineseName(cellValue);
//						break;
//					case MemberUtils.COL_BIRTH_DATE:
//						MemberUtils.setBirthDate(member, cellValue, formatString);
//						break;
//					case MemberUtils.COL_PARISTECH_ENTRANCE_YEAR:
//						MemberUtils.setParisTechEntranceYear(member, cellValue);
//						break;
//					case MemberUtils.COL_PARISTECH_SCHOOL:
//						member.setParisTechSchool(cellValue);
//						break;
//					case MemberUtils.COL_CHINESE_UNIVERSITY:
//						member.setChineseUniversity(cellValue);
//						break;
//					case MemberUtils.COL_SCHOLARSHIP:
//						member.setScholarship(cellValue);
//						break;
//					case MemberUtils.COL_DOMAIN_IN_FRANCE:
//						member.setDomainInFrance(cellValue);
//						break;
//					case MemberUtils.COL_DOMAIN_IN_CHINA:
//						member.setDomainInChina(cellValue);
//						break;
//					case MemberUtils.COL_OTHER_DIPLOMA:
//						member.setOtherDiploma(cellValue);
//						break;
//					case MemberUtils.COL_DIPLOMA_UNIV:
//						member.setDiplomaUniversity(cellValue);
//						break;
//					case MemberUtils.COL_FIRST_JOB_COUNTRY:
//						member.setFirstJobCountry(cellValue);
//						break;
//					case MemberUtils.COL_INTERNSHIP:
//						member.setInternship(cellValue);
//						break;
//					case MemberUtils.COL_FIRST_JOB:
//						member.setFirstJob(cellValue);
//						break;
//					case MemberUtils.COL_JOB_COUNTRY:
//						member.setJobCountry(cellValue);
//						break;
//					case MemberUtils.COL_EMPLOYER:
//						member.setEmployer(cellValue);
//						break;
//					case MemberUtils.COL_EMAIL:
//						member.setEmail(cellValue);
//						break;
//					case MemberUtils.COL_PHONE:
//						MemberUtils.setPhone(member, cellValue);
//						break;
//					case MemberUtils.COL_ADDRESS:
//						member.setAddress(cellValue);
//						break;
//					case MemberUtils.COL_COUNTRY:
//						member.setCountry(cellValue);
//						break;
//					case MemberUtils.COL_WEIBO:
//						member.setWeibo(cellValue);
//						break;
//					case MemberUtils.COL_WEIXIN:
//						member.setWeixin(cellValue);
//						break;
//					case MemberUtils.COL_NOTES:
//						member.setNotes(cellValue);
//						break;
//					}
//					
//				}
			
//			}
		}

		/**
		 * Captures characters only if a suitable element is open. Originally
		 * was just "v"; extended for inlineStr also.
		 */
		public void characters(char[] ch, int start, int length)
				throws SAXException {
			if (vIsOpen)
				value.append(ch, start, length);
		}

//		/**
//		 * Converts an Excel column name like "C" to a zero-based index.
//		 * 
//		 * @param name
//		 * @return Index corresponding to the specified name
//		 */
//		private int nameToColumn(String name) {
//			int column = -1;
//			for (int i = 0; i < name.length(); ++i) {
//				int c = name.charAt(i);
//				column = (column + 1) * 26 + c - 'A';
//			}
//			return column;
//		}

		/**
		 * Converts an Excel cell reference like "C4" to a 0-based column index like 2.
		 * 
		 * @param ref
		 * @return Index corresponding to the specified reference
		 */
		private int refToColumn(String ref) {
			int firstDigit = _getFirstDigit(ref);
			String name = ref.substring(0, firstDigit);

			int column = -1;
			for (int i = 0; i < name.length(); ++i) {
				int c = name.charAt(i);
				column = (column + 1) * 26 + c - 'A';
			}
			return column;
		}
		
//		private long refToRow(String ref) {
//			int firstDigit = _getFirstDigit(ref);
//			String col = ref.substring(firstDigit);
//			return Long.parseLong(col) - 1;
//		}
		
		private int _getFirstDigit(String ref) {
			for (int c = 0; c < ref.length(); ++c) {
				if (Character.isDigit(ref.charAt(c))) {
					return c;
				}
			}
			return -1;
		}
		
		private void flushMembers() {
			uploadService.flushUploadMember(uploader);
		}

	}
	
	private static boolean empty(String str) {
		return str == null || str.isEmpty();
	}

	// /////////////////////////////////////

//	private OPCPackage xlsxPackage;
//	private int minColumns;
//	private PrintStream output;
//	private AlumniFile file;

//	/**
//	 * Creates a new XLSX -> CSV converter
//	 * 
//	 * @param pkg
//	 *            The XLSX package to process
//	 * @param output
//	 *            The PrintStream to output the CSV to
//	 * @param minColumns
//	 *            The minimum number of columns to output, or -1 for no minimum
//	 */
//	public XLSX2CSV(OPCPackage pkg, PrintStream output, int minColumns, AlumniFile file) {
//		this.xlsxPackage = pkg;
//		this.output = output;
//		this.minColumns = minColumns;
//		this.file = file;
//	}

	/**
	 * Parses and shows the content of one sheet using the specified styles and
	 * shared-strings tables.
	 * 
	 * @param styles
	 * @param strings
	 * @param sheetInputStream
	 */
	void processSheet(StylesTable styles,
			ReadOnlySharedStringsTable strings, InputStream sheetInputStream,
			int minColumns, AlumniFile file, List<Message> failures)
			throws IOException, ParserConfigurationException, SAXException {

		InputSource sheetSource = new InputSource(sheetInputStream);
		SAXParserFactory saxFactory = SAXParserFactory.newInstance();
		SAXParser saxParser = saxFactory.newSAXParser();
		XMLReader sheetParser = saxParser.getXMLReader();
		MyXSSFSheetHandler handler = new MyXSSFSheetHandler(styles, strings,
				minColumns, file, failures);
		sheetParser.setContentHandler(handler);
		sheetParser.parse(sheetSource);
		handler.flushMembers();
	}

	/**
	 * Initiates the processing of the XLS workbook file to CSV.
	 * 
	 * @throws IOException
	 * @throws OpenXML4JException
	 * @throws ParserConfigurationException
	 * @throws SAXException
	 */
	void process(InputStream inputStream, ExcelFile file,
			List<Message> failures) throws IOException, OpenXML4JException,
			ParserConfigurationException, SAXException {
		
		OPCPackage xlsxPackage = OPCPackage.open(inputStream);
		int minColumns = defaultMinColumns;

		ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(
				xlsxPackage);
		XSSFReader xssfReader = new XSSFReader(xlsxPackage);
		StylesTable styles = xssfReader.getStylesTable();
		XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader
				.getSheetsData();
		int index = 1;
		int processedSheetNumber = file.getSheetNumber();
		while (iter.hasNext() && index <= processedSheetNumber) {
			InputStream stream = iter.next();
			
//			String sheetName = iter.getSheetName();
//			output.println();
//			output.println(sheetName + " [index=" + index + "]:");
			if (index == processedSheetNumber) {
				processSheet(styles, strings, stream, minColumns, file, failures);
				stream.close();
				++index;
			}
		}
		
		// previously flushed here.
	}

}
